﻿using HRM.Model;
using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HRM.Helper
{
    public class ExportExcelStaff:IExportExcel<NhanVien>
    {
        
       public void ExportExel(List<NhanVien> items, string sheetName, string title)
        {
            Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
            Workbooks workbooks;
            Sheets sheets;
            Workbook workbook;
            Worksheet worksheet;
            oExcel.Visible = true;
            oExcel.DisplayAlerts = false;
            
            oExcel.Application.SheetsInNewWorkbook = 1;
            workbooks = oExcel.Workbooks;

            workbook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
            sheets = workbook.Worksheets;
            worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
            worksheet.Name = sheetName;
            // Tạo header cho báo cáo
            Microsoft.Office.Interop.Excel.Range head = worksheet.get_Range("A1", "N1");
            head.MergeCells = true;
           head.Value2 = title;
            head.Font.Bold = true;
           head.Font.Name = "Tahoma";
            head.Font.Size = "18";
            head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            // Tạo header cho report
            Microsoft.Office.Interop.Excel.Range cl1 = worksheet.get_Range("A3", "A3");
            cl1.Value2 = "ID";
            cl1.ColumnWidth = 13.5;
            Microsoft.Office.Interop.Excel.Range cl2 = worksheet.get_Range("B3", "B3");
            cl2.Value2 = "MaTDHV";
            cl2.ColumnWidth = 25.0;
            Microsoft.Office.Interop.Excel.Range cl3 = worksheet.get_Range("C3", "C3");
            cl3.Value2 = "FullName";
            cl3.ColumnWidth = 25.0;
            
            Microsoft.Office.Interop.Excel.Range cl4 = worksheet.get_Range("D3", "D3");
            cl4.Value2 = "DateOfBirth";
            cl4.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl5 = worksheet.get_Range("E3", "E3");
            cl5.Value2 = "Gender";
            cl5.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl6 = worksheet.get_Range("F3", "F3");
            cl6.Value2 = "CMND";
            cl6.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl7 = worksheet.get_Range("G3", "G3");
            cl7.Value2 = "Date of Grant";
            cl7.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl8 = worksheet.get_Range("H3", "H3");
            cl8.Value2 = "Place of Grant";
            cl8.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl9 = worksheet.get_Range("I3", "I3");
            cl9.Value2 = "SDT";
            cl9.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl10 = worksheet.get_Range("J3", "J3");
            cl10.Value2 = "Address";
            cl10.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl11 = worksheet.get_Range("K3", "K3");
            cl11.Value2 = "Nation";
            cl11.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl12 = worksheet.get_Range("L3", "L3");
            cl12.Value2 = "Email";
            cl12.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl13 = worksheet.get_Range("M3", "M3");
            cl13.Value2 = "QueQuan";
            cl13.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range cl14 = worksheet.get_Range("N3", "N3");
            cl14.Value2 = "MaSoThue";
            cl14.ColumnWidth = 30.0;
            Microsoft.Office.Interop.Excel.Range rowHead = worksheet.get_Range("A3", "N3");
            rowHead.Font.Bold = true;
            // Kẻ viền
            rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            // Thiết lập màu nền
            rowHead.Interior.ColorIndex = 15;
            rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            //use object storage data
            NhanVien nv = new NhanVien();
            //get number attribute of NhanVien
            int columns = typeof(NhanVien).GetProperties().Count();
            
            int rows = items.Count;
            object[,] arr = new object[rows, columns];
            for (int r = 0; r < rows; r++)
            {

                arr[r, 0] = items[r].MaNhanVien;
                arr[r, 1] = items[r].MaTDHV;
                arr[r, 2] = items[r].HoTen;
                arr[r, 3] = items[r].NgaySinh;
                arr[r, 4] = items[r].GioiTinh;
                arr[r, 5] = items[r].CMND;
                arr[r, 6] = items[r].NgayCapCMND;
                arr[r, 7] = items[r].NoiCapCMND;
                arr[r, 8] = items[r].SDT;
                arr[r, 9] = items[r].DiaChi;
                arr[r, 10] = items[r].DanToc;
                arr[r, 11] = items[r].Email;
                arr[r, 12] = items[r].QueQuan;
                arr[r, 13] = items[r].MaSoThue;
            }
            int rowStart = 4;
            int columnStart = 1;

            int rowEnd = rowStart + items.Count - 1;
            int columnEnd = columns;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;

            //range.Value2 = (System.Data.DataTable)li;
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            // Căn giữa cột STT
            Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnStart];
            Microsoft.Office.Interop.Excel.Range c4 = worksheet.get_Range(c1, c3);
            worksheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        }
    }
    
}
